package com.yigerende.dao;


import com.yigerende.model.Blog;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

//继承这个接口JpaSpecificationExecutor<Blog>，就可以选择性查询了
public interface BlogRepository extends JpaRepository<Blog,Long> , JpaSpecificationExecutor<Blog> {

    //通过每一个标签有多少个博客数量来排序的(index页面右边)
    //自定义查询(别名用表名的第一个字母)
    //这里查询的是推荐的博客加上(where b.recommend = true)
    @Query("select b from Blog b where b.recommend = true")
    public List<Blog> findTop(Pageable pageable);


    /**全局搜索
    1.b是Blog别名
    2.标题title或者content内容进行模糊查询
    3.?1就是查询传递过来的第一个参数(query)
     */
    @Query("select b from Blog b where b.title like ?1 or b.content like ?1")
    public Page<Blog> findByQuery(String query,Pageable pageable);



    //自动更新查看次数
    @Transactional
    @Modifying
    @Query("update Blog b set b.views = b.views+1 where b.id = ?1")//自定义更新，修改查看次数views
    public int updateViews(Long id);


    //归档查询
    /**
     * select date_format(b.update_time, '%Y') as year from blog b GROUP by year ORDER BY year DESC
     * 使用function传递date_format方法
     * 使用date_format格式化日期只要年份，让year年份进行排序
     * 用year重列名，group by按年份分组，order by排序
     * */
    @Query("select function('date_format',b.updateTime,'%Y') as year from Blog b group by function('date_format',b.updateTime,'%Y') order by year desc ")
    public List<String> findGroupYear();

    //归档根据年份查询
    @Query("select b from Blog b where function('date_format',b.updateTime,'%Y') = ?1")
    public List<Blog> findByYear(String year);
}
